package no.ntnu.tdt4237;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.security.SecureRandom;
import java.io.UnsupportedEncodingException;
import java.math.BigInteger;

import no.ntnu.tdt4237.helperactions.DateHelpers;
import no.ntnu.tdt4237.helperactions.FilterXSS;

/*
 *  Layer for interaction between the model classes 
 *  and the database
 */
public class Database {

	private static Connection createConnection()
	{
		Connection conn;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance(); 
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/"+Config.SQL_DB,
					Config.SQL_USERNAME, Config.SQL_PASSWORD
					);
		} catch (Exception e) {
			e.printStackTrace();
			conn = null;
		} 

		return conn;
	}	

	public static boolean login(String username, String password) {
		
		Connection conn = createConnection();

		boolean authenticated = false;

		try {
			String query = "SELECT * FROM BlogUser WHERE username= ? AND password= ? AND activated = ? ";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, username);
			String passHash = getHash(password);
			st.setString(2, passHash.toString());
			st.setInt(3, 1);
			ResultSet rs  = st.executeQuery();
			
			if (rs.next()) {
				if(rs.getInt("failedlogin") == 0) {
					authenticated = true;
				}
				else if(rs.getInt("failedlogin") <= 4) {
					authenticated = true;
					query = "UPDATE `BlogUser` SET failedlogin = 0 WHERE username = ? ";
					st = conn.prepareStatement(query);
					st.setString(1, username);
					st.executeUpdate();
				}
				
			}
			else {
				query = "SELECT * FROM BlogUser WHERE username= ? AND activated = ? ";
				PreparedStatement st2 = conn.prepareStatement(query);
				st2.setString(1, username);
				st2.setInt(2, 1);
				rs  = st2.executeQuery();
				if(rs.next()) {
					if(rs.getInt("failedlogin") < 4) {
						query = "UPDATE `BlogUser` SET failedlogin = failedlogin + 1 WHERE username = ? AND activated = ? ";
						st2 = conn.prepareStatement(query);
						st2.setString(1, username);
						st2.setInt(2, 1);
						st2.executeUpdate();
					}
					else if(rs.getInt("failedlogin") == 4) {
						query = "UPDATE `BlogUser` SET failedlogin = ? ,  activated = ?  WHERE username = ? ";
						st2 = conn.prepareStatement(query);
						st2.setInt(1, 5);
						st2.setInt(2, 0);
						st2.setString(3, username);
						st2.executeUpdate();
						Activation.recoverAccount(rs.getString("username"), rs.getString("email"));
					}
				
				}
				st2.close();
			}
			
			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} 

		return authenticated;
	}

	public static boolean saveUser(User u)
	{
		Connection conn = createConnection();

		boolean result;

		if( u != null)
		{
			if(!userExist(u))
			{
				try{
					String query = "INSERT INTO BlogUser(username,password,email,fname,lname,activated) VALUES( ? , ? , ? , ? , ? , ? );";
					PreparedStatement st = conn.prepareStatement(query);
					st.setString(1, FilterXSS.filter(u.getUserName()));
					String passHash = getHash(u.getPassword());
					st.setString(2, passHash.toString());
					st.setString(3, u.getEmail());
					st.setString(4, FilterXSS.filter(u.getFirstName()));
					st.setString(5, FilterXSS.filter(u.getLastName()));
					st.setInt(6, 0);
					st.executeUpdate();

					st.close();
					conn.close();
					result = true;

				} catch (Exception e) {
					e.printStackTrace();
					result = false;
				} 
			}
			else
			{
				try{
					String query = "UPDATE BlogUser SET password= ? , email= ? , fname= ? , lname= ?, username= ?  WHERE userid= ? ";
					PreparedStatement st = conn.prepareStatement(query);
					String passHash = getHash(u.getPassword());
					st.setString(1, passHash.toString());
					st.setString(2, u.getEmail());
					st.setString(3, FilterXSS.filter(u.getFirstName()));
					st.setString(4, FilterXSS.filter(u.getLastName()));
					st.setString(5, FilterXSS.filter(u.getUserName()));
					st.setInt(6, u.getUserID());
					st.executeUpdate();

					st.close();
					conn.close();
					result = true;

				} catch (Exception e) {
					e.printStackTrace();
					result = false;
				}
			}
		}
		else
		{
			result = false;
		}

		return result;
	}

	private static boolean userExist(User u) {
		ArrayList<User> users = getUsers();

		boolean result = false;
		
		for(User user: users)
		{
			if(user.getUserID() == u.getUserID()) {
				result = true;
			}
		}

		return result;
	}

	public static ArrayList<User> getUsers()
	{
		ArrayList<User> list = new ArrayList<User>();

		Connection conn = createConnection();

		try{
			String query = "SELECT * FROM BlogUser;";
			PreparedStatement st = conn.prepareStatement(query);
			ResultSet rs = st.executeQuery();

			while(rs.next())
			{
				list.add(new User(rs.getInt("userid"), rs.getString("username"),rs.getString("password"),rs.getString("email"),rs.getString("fname"),rs.getString("lname")));
			}

			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} 

		return list;
	}

	public static User getUser(String username)
	{
		ArrayList<User> users =getUsers();
		User result = null;

		for(User u: users)
		{
			if(u.getUserName().equals(username)) result = u;
		}

		return result;
	}
	
	public static User getUser(int userid)
	{
		ArrayList<User> users =getUsers();
		User result = null;
		
		for(User u: users)
		{
			if(u.getUserID() ==  userid) result = u;
		}
		
		return result;
	}


	public static boolean savePost(BlogPost p)
	{
		Connection conn = createConnection();

		boolean result;

		if( p != null)
		{
			if(!postExist(p))
			{
				try{
					

					String query = "INSERT INTO BlogPost(title,text,date,owner,picture) VALUES( ? , ? , ? , ? , ? )";
					PreparedStatement st = conn.prepareStatement(query);
					st.setString(1, FilterXSS.filter(p.getTitle()));
					st.setString(2, FilterXSS.filter(p.getText()));
					st.setDate(3, DateHelpers.utilDateToSqlDate(p.getDate()));
					st.setInt(4, p.getOwner().getUserID());
					st.setString(5, FilterXSS.filter(p.getPictureName()));
					st.executeUpdate();

					st.close();
					conn.close();
					result = true;

				} catch (Exception e) {
					e.printStackTrace();
					result = false;
				} 
			}
			else
			{
				try{


					String query = "UPDATE BlogPost SET text= ? , date= ? WHERE blogid= ? ";
					PreparedStatement st = conn.prepareStatement(query);
					st.setString(1, FilterXSS.filter(p.getText()));
					st.setDate(2, DateHelpers.utilDateToSqlDate(p.getDate()));
					st.setInt(3, getPostId(p));
					st.executeUpdate();

					st.close();
					conn.close();
					result = true;

				} catch (Exception e) {
					e.printStackTrace();
					result = false;
				}
			}
		}
		else
		{
			result = false;
		}

		return result;
	}

	public static boolean editPost(BlogPost oldPos, BlogPost newPost)
	{
		if (oldPos !=null && newPost != null)
		{
			int postId = getPostId(oldPos);
			if (postId >= 0)
			{
				Connection conn = createConnection();
				boolean result = false;
				try
				{
					String query = "UPDATE BlogPost SET text= ? , date= ? WHERE blogid= ? ";
					PreparedStatement st = conn.prepareStatement(query);
					st.setString(1, FilterXSS.filter(newPost.getText()));
					st.setDate(2, DateHelpers.utilDateToSqlDate(newPost.getDate()));
					st.setInt(3, postId);					
					st.executeUpdate();

					st.close();
					conn.close();
					result = true;

				} 
				catch (Exception e) {
					e.printStackTrace();
					result = false;
				}
				return result;
			}

		}
		return false;
	}

	public static boolean deletePost(BlogPost p)
	{
		Connection conn = createConnection();

		boolean result;

		if( p != null)
		{
			if(postExist(p))
			{
				try{
					String query = "DELETE FROM `BlogPost` WHERE `title` = ? AND `date` = ? AND `owner` = ? LIMIT 1;";
					PreparedStatement st = conn.prepareStatement(query);
					st.setString(1, p.getTitle());
					st.setDate(2, DateHelpers.utilDateToSqlDate(p.getDate()));
					st.setInt(3, p.getOwner().getUserID());
					st.execute();
					result = st.getUpdateCount() == 1;
					st.close();
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
					result = false;
				} 
			}
			else
			{
				result = false;
			}
		}
		else
		{
			result = false;
		}

		return result;
	}

	public static boolean deleteComment(Comment c)
	{
		Connection conn = createConnection();

		boolean result;

		if( c != null)
		{
			int postId = getPostId(c.getPost());
			if (postId >= 0)
			{
				try{
					String query = "DELETE FROM `BlogComment` WHERE `owner` = ? AND `postid` = ? AND `commentid` = ? LIMIT 1";
					PreparedStatement st = conn.prepareStatement(query);
					if(c.getOwner() != null)
						st.setInt(1, c.getOwner().getUserID());
					else
						st.setInt(1, 0);
					st.setInt(2, postId);
					st.setInt(3, c.getCommID());
					st.execute();
					result = st.getUpdateCount() == 1;
					st.close();
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
					result = false;
				} 
			}
			else
			{
				result = false;
			}
		}
		else
		{
			result = false;
		}

		return result;
	}

	public static ArrayList<BlogPost> getPostForUser(User u)
	{
		ArrayList<BlogPost> list = new ArrayList<BlogPost>();

		Connection conn = createConnection();

		try{
			String query = "SELECT * FROM BlogPost WHERE owner= ? ";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, u.getUserID());
			ResultSet rs = st.executeQuery();

			while(rs.next())
			{
				list.add(new BlogPost(rs.getInt("blogid"), rs.getString("title"),rs.getString("text"),rs.getDate("date"),getUser(rs.getInt("owner")),rs.getString("picture")));
			}

			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} 

		return list;
	}

	public static boolean saveComment(Comment c)
	{
		Connection conn = createConnection();

		boolean result;

		if( c != null)
		{

			try{
				String query = "INSERT INTO BlogComment(owner,text,date,postid) VALUES( ? , ? , ? , ? )";
				PreparedStatement st = conn.prepareStatement(query);
				if(c.getOwner() != null)
					st.setInt(1, c.getOwner().getUserID());
				else
					st.setInt(1, 0);
				st.setString(2, FilterXSS.filter(c.getText()));
				st.setDate(3, DateHelpers.utilDateToSqlDate(c.getDate()));
				st.setInt(4, getPostId(c.getPost()));
				st.executeUpdate();

				st.close();
				conn.close();
				result = true;

			} catch (Exception e) {
				e.printStackTrace();
				result = false;
			} 
		}
		else
		{
			result = false;
		}

		return result;
	}


	private static int getPostId(BlogPost p) {

		int res = -1;
		Connection conn = createConnection();

		try{
			String query = "SELECT * FROM BlogPost WHERE owner= ? ";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, p.getOwner().getUserID());
			ResultSet rs = st.executeQuery();

			while(rs.next())
			{
				int id = rs.getInt("blogid");
				String title = rs.getString("title");
				Date date = rs.getDate("date");
				int userid = rs.getInt("owner");

				boolean titleMatch = title.equals(p.getTitle());
				boolean dateMatch = DateHelpers.compareDates(DateHelpers.utilDateToSqlDate(p.getDate()), date);
				boolean userMatch = userid == p.getOwner().getUserID();

				if(titleMatch && dateMatch && userMatch)
					res = id;
			}

			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} 

		return res;
	}


	private static boolean postExist(BlogPost p) {
		boolean res = false;

		Connection conn = createConnection();

		try{
			String query = "SELECT * FROM BlogPost WHERE owner= ? ";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, p.getOwner().getUserID());
			ResultSet rs = st.executeQuery();

			while(rs.next())
			{
				String title = rs.getString("title");
				Date date = rs.getDate("date");
				int userid = rs.getInt("owner");

				boolean titleMatch = title.equals(p.getTitle());
				boolean dateMatch = DateHelpers.compareDates(DateHelpers.utilDateToSqlDate(p.getDate()), date);
				boolean userMatch = userid == p.getOwner().getUserID();

				if(titleMatch && dateMatch && userMatch)
					res = true;
			}

			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} 

		return res;

	}

	//Not tested
	public static ArrayList<Comment> getPostComments(BlogPost p)
	{
		ArrayList<Comment> list = new ArrayList<Comment>();

		Connection conn = createConnection();

		try{
			String query = "SELECT * FROM BlogComment WHERE postid= ? ;";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, getPostId(p));
			ResultSet rs = st.executeQuery();

			while(rs.next())
			{
				if(rs.getString("owner") != null)
					list.add(new Comment(rs.getInt("commentid"), getUser(rs.getInt("owner")),rs.getString("text"),rs.getDate("date"),p));
				else
					list.add(new Comment(rs.getInt("commentid"), rs.getString("text"),rs.getDate("date"),p));
			}

			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} 

		return list;
	}

	public static BlogPost getPost(User u , int blogid)
	{
		ArrayList<BlogPost> list = new ArrayList<BlogPost>();

		Connection conn = createConnection();

		try{
			String query = "SELECT * FROM BlogPost WHERE owner= ?  AND blogid= ? ";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, u.getUserID());
			st.setInt(2, blogid);
			ResultSet rs = st.executeQuery();

			while(rs.next())
			{
				list.add(new BlogPost(rs.getInt("blogid"), rs.getString("title"),rs.getString("text"),rs.getDate("date"),getUser(rs.getInt("owner")), rs.getString("picture")));
			}

			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} 

		if(list.size() > 0)
			return list.get(0);
		return null;

	}

	public static Boolean saveActivation(String username, String code) {
		Connection conn = createConnection();
		boolean result;
		try{
			String query = "INSERT INTO BlogUserActivation(username, code) VALUES( ? , ? )";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, username);
			st.setString(2, code);
			st.executeUpdate();

			st.close();
			conn.close();
			result = true;

		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}

		return result;
	}
	
	public static Boolean validateActivation(String username, String code) {
		Connection conn = createConnection();
		boolean result = false;
		try{
			String query = "SELECT * FROM BlogUserActivation WHERE username = ? AND code = ?";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, username);
			st.setString(2, code);
			ResultSet resultSet  = st.executeQuery();
			
			if (resultSet.next()) {
				result = true;
				resultSet.close();
			}
			if(result) {
				st.close();
				query = "UPDATE BlogUser SET activated= ?, failedlogin = ? WHERE username= ? ";
				PreparedStatement st2 = conn.prepareStatement(query);
				st2.setInt(1, 1);
				st2.setInt(2, 0);
				st2.setString(3, username);
				st2.executeUpdate();
				st2.close();
				
				query = "DELETE FROM `BlogUserActivation` WHERE `username` = ? LIMIT 1";
				PreparedStatement st3 = conn.prepareStatement(query);
				st3.setString(1, username);
				st3.execute();
				st3.close();
			}
			
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}

		return result;
	}
	
	/**
	 * Hashing function v1.0
	 * @param password
	 * @return input
	 * @throws NoSuchAlgorithmException
	 * @throws UnsupportedEncodingException 
	 */
	public static String getHash(String password) throws NoSuchAlgorithmException, UnsupportedEncodingException {
	       MessageDigest digest = MessageDigest.getInstance("SHA-1");
	       digest.reset();
	       byte[] input = digest.digest(password.getBytes("UTF-8"));
	       BigInteger hash = new BigInteger(1, input);
	       // System.out.print(" pass: " + password + " hash: " + hash.toString(16));
	       return hash.toString(16);
	}

	public static boolean resetPassword(String username, String email) {
//		String code = Activation.generateCode();
		SecureRandom random = new SecureRandom();
		String newPass = new BigInteger(60, random).toString(32);
		String message = "Dear user, we registered a requested for resetting your password.\n\n" +
				"Your new account information:\n" + "\n" +
				"login: \t" + username + "\n" +
				"password: \t" + newPass;
		System.out.print("Resetting password...");
		//Boolean success = Database.saveActivation(username, code);
		Boolean success = resetPasswordDB(username, email, newPass);
		if(success)
		//	MailSender.sendActivationCode(email, message);
			System.out.print("DB OK, sending mail");
			MailSender.sendNewPassword(email, message);
		return success;
	}
	
	public static Boolean resetPasswordDB(String username, String email, String password) {
		Connection conn = createConnection();
		boolean result = false;
		try{
			String query = "SELECT * FROM BlogUser WHERE username = ? AND email = ?";
			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, username);
			st.setString(2, email);
			ResultSet resultSet  = st.executeQuery();
			
			if (resultSet.next()) {
				result = true;
				resultSet.close();
			}
			if(result) {
				st.close();
				String passHash = getHash(password);
				query = "UPDATE BlogUser SET password= ? WHERE username= ? AND email = ?";
				PreparedStatement st2 = conn.prepareStatement(query);
				st2.setString(1, passHash.toString());
				st2.setString(2, username);
				st2.setString(3, email);
				st2.executeUpdate();
				st2.close();
			}
			
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}

		return result;
	}
	
}
